
/*************************************************
Program: Stata_04a_combine_census_bea.sas
Author: hannah rubinton
Date: 1/31/2019

input: 
firm_2007_withtypes.dta created in stata04a
firm_import.dta created in stata_03
bea data created in stata_02

outputs: 
firmXcountry dataset: ./output/country_imp_aff.dta



Notes: 
Creates firmXcountry level dataset by merging together census and affiiliate data

**make firm X country dataset by merging together the firm import data and the bea data 
**make sample restrictions to firms that have either domestic or foreign M


* Jack update 05/08/2023 - use the new UN regions

**************************************************/
cd "/"  /* PROJECT ROOT FOLDER */

global data "data"
global input_country input/country_data_input


cap program drop combine_firm_country_data
program define combine_firm_country_data
  syntax , year(integer)
 
  /*****************************************
  **make firm X country dataset by merging together the firm import data and the bea data 
  ******************************************/
  *merge together the firm country datasets 
  *start with import country dataset

  use "$data/firm_country_import`year'.dta", clear
  drop if ctry==.

  *merge with export country dataset
  merge 1:1 firmid ctry using "$data/firm_country_export`year'.dta"
  drop _merge

  *merge with bea outward dataset
  merge 1:1 firmid ctry using "$data/aff_firm_country_`year'.dta"  //country_name missing from this file!!!
  drop _merge 
  
   
  tempfile firm_country_merged
  save `firm_country_merged'


  *merge the firmXcountry data into the firm datasets 
  use "$data/firm_`year'_withtypes.dta", clear
  
   keep firmid lvap_r emp* sales* estabs* tot_inputs* ipt cm ww pay_lbd naics4_primary n4_main_sales ///
		bea_manuf mnl for_mnl_country cos_status formnl_country in_* status_bea in_cos in_bea mne_status ///
		mne_status_supp year foreign_manufacturing type_d type firmage
  

  
  merge 1:m firmid using `firm_country_merged' 
  drop if _merge==2
  drop _merge 


  *Add country variables
    rename ctry countrycode
    merge m:1 countrycode using "$data/country_data_unique_un.dta"
    rename countrycode ctry
    drop if firmid==""
    drop if ctry==.  /*  drop the weird countries not in bea data */
    drop _merge
    
  *make an indicator for foreign-owned country  
   gen for=1 if for_mnl_country==iso3
     replace for=0 if for==.
   

  *Make dataset of firm importers, MNEs, etc.
  *******************************************

  *Make importer and affiliate count vars
  replace aff_sales=0 if aff_sales==.
  replace aff_emp=0 if aff_emp==.
  gen aff=0
  replace aff=1 if aff_emp>0 | aff_sales>0
  replace imp_value=0 if imp_value==.
  gen importer=1 if imp_value>0  
  replace importer=0 if imp_value==0
  replace exp_value=0 if exp_value==.
  gen exporter=1 if exp_value>0  
  replace exporter=0 if exp_value==0
  gen sales_pos=1 if sales>0 & sales~=.
  
    

  *only keep firmXcountry pairs with some foreign activity
  keep if aff==1 | importer==1 | exporter==1 | for==1


  *Scale imports to 000s
  replace imp_value=imp_value/1000
  replace rp_imp_value=rp_imp_value/1000
  replace exp_value=exp_value/1000
  replace rp_exp_value=rp_exp_value/1000


  tab type aff, miss
  tab type importer, miss
  tab type exporter, miss
  tab aff if substr(type,1,3)=="MAN"

  *Limit to sample
  keep if type=="MAN" | type=="MAN MPRO"

  *create an indicator for manufacturing affiliate
  rename man_aff number_man_affiliates /*  no idea what this is  */
  gen man_aff=1 if (man_aff_sales>0 & man_aff_sales~=.) | (man_aff_emp>0 & man_aff_emp~=.)
  replace man_aff = 0 if man_aff==.

  di "save final dataset for `year'"
  save "$data/country_imp_aff`year'.dta", replace



end // combine_firm_data


combine_firm_country_data, year(2007)
*combine_firm_country_data, year(2012)

di "made it"
